﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using System.Configuration;
using Com.Crc.PoJo.Model.UserBoard;
using SnailGame.Utility.DataAccess;
using Com.Crc.PoJo.Model.Resource;

namespace Com.Crc.ResManage.DAL.EquipManage
{
    public class PONManageUserBoardDAL
    {
        DataBroker broker = new DataBroker();

        /// <summary>
        /// 新增PON设备用户板信息
        /// </summary>
        /// <param name="data">model实体</param>
        /// <returns></returns>
        public bool AddPONUserBoardData(PONUserBoardEntity data)
        {
            string strSql = @" INSERT INTO TT_USERBOARD_PON(N_OLT_ID,N_SLOT_NO,S_TYPE,N_QNQVLAN_S,N_QNQVLAN_E,S_STATE)
                                    VALUES (" + data.N_OLT_ID + "," + data.N_SLOT_NO + "," + data.S_TYPE + ","
                                           + data.N_QNQVLAN_S + "," + data.N_QNQVLAN_E + ",'1')";
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

        /// <summary>
        /// 修改PON设备用户板信息
        /// </summary>
        /// <param name="data">model实体</param>
        /// <returns></returns>
        public bool UpdatePONUserBoardData(PONUserBoardEntity data)
        {
            string strSql = @" UPDATE TT_USERBOARD_PON
                                  SET N_OLT_ID = " + data.N_OLT_ID + @", 
                                      N_SLOT_NO = " + data.N_SLOT_NO + @",
                                      S_TYPE = " + data.S_TYPE + @", 
                                      N_QNQVLAN_S = " + data.N_QNQVLAN_S + @",
                                      N_QNQVLAN_E = " + data.N_QNQVLAN_E + @",
                                      S_STATE = " + data.S_STATE + @"
                                WHERE N_ID=" + data.N_ID;
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

        /// <summary>
        /// 查询PON设备用户板信息
        /// </summary>
        /// <param name="strBranchID">分局ID</param>
        /// <param name="strOfficeID">局点ID</param>
        /// <param name="strOltID">OLT设备ID</param>
        /// <param name="strUserBoardID">用户板ID</param>
        /// <returns></returns>
        public DataTable QueryPONUserBoardData(string strBranchID, string strOfficeID, string strOltID, string strUserBoardID)
        {
            string strSql = @" SELECT a.N_ID,a.N_OLT_ID,a.N_SLOT_NO,a.S_TYPE,b.S_NAME AS S_PON_USERBOARD_TYPE,a.N_QNQVLAN_S,a.N_QNQVLAN_E,
                                      b.S_NAME AS S_OLT_NAME
                                 FROM TT_USERBOARD_PON a
                           INNER JOIN TT_RESOURCE_OLT b ON a.N_OLT_ID=b.N_ID
                           INNER JOIN TT_RESOURCE_DICTIONARY c ON a.S_TYPE = c.N_ID AND c.S_TYPE=" + (int)Dictionary.COMMON_DIC_TYPE.RESOURCE_OLT_PONUSERBOARD_TYPE + @"
                                WHERE a.S_STATE='1' ";
            if (strBranchID != "")
            {
                strSql += " AND b.N_DEP_BRANCH_ID = " + strBranchID;
            }
            if (strOfficeID != "")
            {
                strSql += " AND b.N_SUBSTATION_ID = " + strOfficeID;
            }
            if (strOltID != "")
            {
                strSql += " AND a.N_OLT_ID = " + strOltID;
            }
            if (strUserBoardID != "")
            {
                strSql += " AND a.N_ID = " + strUserBoardID;
            }
            return broker.ExecuteQueryForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }
        
        /// <summary>
        /// 删除PON下面的用户板，修改状态位
        /// </summary>
        /// <param name="wlanList"></param>
        /// <returns></returns>
        public bool DeletePONUserBoardData(ArrayList userboardList)
        {
            string strPONUserboardID = "";
            for (int i = 0; i < userboardList.Count; i++)
            {
                strPONUserboardID += userboardList[i] + ",";
            }
            strPONUserboardID = strPONUserboardID.Substring(0, strPONUserboardID.Length - 1);
            string strSql = @"UPDATE TT_USERBOARD_PON SET S_STATE='0' WHERE N_ID IN (" + strPONUserboardID + ")";
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

        /// <summary>
        /// 删除PON下面的用户板，修改状态位
        /// </summary>
        /// <param name="wlanList"></param>
        /// <returns></returns>
        public bool DeletePONUserBoardDataByOLT(ArrayList arrOltIDList)
        {
            string strPONOltID = "";
            for (int i = 0; i < arrOltIDList.Count; i++)
            {
                strPONOltID += arrOltIDList[i] + ",";
            }
            string strSql = @"UPDATE TT_USERBOARD_PON SET S_STATE='0' WHERE N_OLT_ID IN (" + strPONOltID + ")";
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }
    }
}
